package com.apes.fn.scm.merchant.account.service;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import org.springframework.stereotype.Component;

@Component
public class AccountSettlementDao extends ApesDao {

    /**
     * 分页查询
     * @param param
     * @return
     */
    public JSONArray findDept(JSONObject param,String company,int page, int size) {
        StringBuffer sb = new StringBuffer();

        sb.append(" SELECT * FROM ( SELECT CB.*, ROWNUM AS rowno FROM ( " +
                "SELECT a.id, '[' || A.MERCHANT_ID || '] ' || (SELECT NAME FROM MAS_PARTY WHERE ID = (SELECT P.PARTY_ID FROM MAS_PARTY_ROLE P\n" +
                " WHERE P.ID = A.MERCHANT_ID)) as merchant, (SELECT NAME FROM MAS_PARTY WHERE ID = (SELECT P.PARTY_ID FROM MAS_PARTY_ROLE P\n" +
                " WHERE  P.ID = A.Reconcile_Uid)) reconcileUid, to_char(a.reconcile_date, 'yyyy-mm-dd') as reconcileDate, a.state,\n" +
                " to_char(a.reconcile_begin_date, 'yyyy-mm-dd') as reconcileBeginDate, to_char(a.reconcile_end_date, 'yyyy-mm-dd') as reconcileEndDate,\n" +
                " a.invoice_number as invoiceNumber FROM merchant_account_settlement a where 1=1 \n");
        if (param.get("ID") != null) {
            sb.append(" AND a.id =:ID");
        }
        if (param.get("MERCHANT_ID") != null) {
            sb.append(" AND a.MERCHANT_ID =:MERCHANT_ID");
        }
        if (param.get("RECONCILE_UID") != null) {
            sb.append(" AND a.RECONCILE_UID =:RECONCILE_UID");
        }
        if (param.get("STATE") != null) {
            sb.append(" AND a.STATE =:STATE");
        }
        if (param.get("reconcile_begin_date") != null) {
            sb.append(" AND a.reconcile_begin_date >= to_date(substr(:reconcile_begin_date_DATE,1,10),'yyyy-MM-dd') AND a.reconcile_begin_date < to_date(substr(:reconcile_begin_date,1,10),'yyyy-MM-dd') +1");
        }
        if (param.get("reconcile_end_date") != null) {
            sb.append(" AND a.reconcile_end_date >= to_date(substr(:reconcile_end_date_DATE,1,10),'yyyy-MM-dd') AND a.reconcile_end_date < to_date(substr(:reconcile_end_date,1,10),'yyyy-MM-dd') +1");
        }

        if (param.get("reconcile_date") != null) {
            sb.append(" AND a.reconcile_date >= to_date(:reconcile_date_DATE,'yyyy-MM-dd hh24:mi:ss') AND a.reconcile_date < to_date(:reconcile_date,'yyyy-MM-dd hh24:mi:ss')  ");
        }
        if (param.get("terminal") != null && param.get("terminal").equals("merchantSystem")) {
            sb.append(" AND a.STATE != 'sent'");
        }

        if (!"A0000".equals(company)) {
            sb.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.MERCHANT_ID = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        sb.append(" order by a.create_date desc  ) CB WHERE ROWNUM <="+ (page == 0? size : (page +1)* size)+" )T_CB WHERE T_CB.rowno >"+((page+1)-1) * size);
        return toJSONArray(find(sb.toString(), param));

    }


    /**
     * 分页查询
     * @param param
     * @return
     */
    public Object findCount(JSONObject param ,String company) {
        StringBuffer sb = new StringBuffer();

        sb.append(" SELECT count(1) count FROM merchant_account_settlement a where 1=1 \n");
        if (param.get("ID") != null) {
            sb.append(" AND a.id =:ID");
        }
        if (param.get("MERCHANT_ID") != null) {
            sb.append(" AND a.MERCHANT_ID =:MERCHANT_ID");
        }
        if (param.get("RECONCILE_UID") != null) {
            sb.append(" AND a.RECONCILE_UID =:RECONCILE_UID");
        }
        if (param.get("STATE") != null) {
            sb.append(" AND a.STATE =:STATE");
        }
        if (param.get("reconcile_begin_date") != null) {
            sb.append(" AND a.reconcile_begin_date >= to_date(substr(:reconcile_begin_date_DATE,1,10),'yyyy-MM-dd') AND a.reconcile_begin_date < to_date(substr(:reconcile_begin_date,1,10),'yyyy-MM-dd') +1");
        }
        if (param.get("reconcile_end_date") != null) {
            sb.append(" AND a.reconcile_end_date >= to_date(substr(:reconcile_end_date_DATE,1,10),'yyyy-MM-dd') AND a.reconcile_end_date < to_date(substr(:reconcile_end_date,1,10),'yyyy-MM-dd') +1");
        }

        if (param.get("reconcile_date") != null) {
            sb.append(" AND a.reconcile_date >= to_date(:reconcile_date_DATE,'yyyy-MM-dd hh24:mi:ss') AND a.reconcile_date < to_date(:reconcile_date,'yyyy-MM-dd hh24:mi:ss')  ");
        }
        if (param.get("terminal") != null && param.get("terminal").equals("merchantSystem")) {
            sb.append(" AND a.STATE != 'sent'");
        }

        if (!"A0000".equals(company)) {
            sb.append(" AND ((EXISTS (SELECT 1 " +
                    "                FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                AND ROLE1.ID = '" + company + "' " +
                    "                AND ROLE3.TYPE = 'pt_company' " +
                    "                AND ROLE3.PURPOSE = 'pp_search' " +
                    "                AND A.MERCHANT_ID = ROLE3.ORG_ID) OR " +
                    "                (SELECT NVL(COUNT(1), 0) " +
                    "                        FROM AUT_POSTS ROLE1, AUT_LOCAL_PERMIT ROLE2, " +
                    "                        AUT_LOCAL_PERMIT_ITEM ROLE3 " +
                    "                        WHERE ROLE1.LOCAL_PERMIT_ID = ROLE2.ID " +
                    "                        AND ROLE2.ID = ROLE3.LOCAL_PERMIT_ID " +
                    "                        AND ROLE1.ID = '" + company + "' " +
                    "                        AND ROLE3.TYPE = 'pt_company' " +
                    "                        AND ROLE3.PURPOSE = 'pp_search') = 0))");
        }
        sb.append(" order by a.create_date desc ");
        return toJSONArray(find(sb.toString(), param));

    }

}
